﻿using BaBaoFan.Modifier.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Dapper;

namespace BaBaoFan.Modifier.Data
{
    public class NavigationRepository : INavigationRepository
    {
        private IDbConnection _conn;
        public NavigationRepository(DbConnection conn)
        {
            _conn = conn.Connection;
        }

        public bool AddNavigationColumn(NavigationColumn navigationColumn)
        {
            string sql = "INSERT INTO `navigation_column`  (column_name,img,on_img,state,priority,sv)VALUES (@Name, @Icon,@OnIcon,@State,@Priority,@Sv)";
            return _conn.Execute(sql, navigationColumn) > 0;
        }

        public bool DeleteNavigationColumns(IEnumerable<int> ids)
        {
            string delColumnSql = "update navigation_column set is_del=1 where id in @Ids";
            string delRelationsSql = "delete from navigation_relation where column_id in @Ids";
            IDbTransaction transaction = _conn.BeginTransaction();
            var delNavigationResult = _conn.Execute(delColumnSql, new { Ids = ids });
            var relationResult = _conn.Execute(delRelationsSql, new { Ids = ids });
            transaction.Commit();
            return true;
        }

        public bool DeleteNavigationInfos(IEnumerable<int> ids)
        {
            string delNavigationSql = "update navigation_info set is_del=1 where id in @Ids";
            string delRelationsSql = "delete from navigation_relation where info_id in @Ids";
            IDbTransaction transaction = _conn.BeginTransaction();
            var delNavigationResult = _conn.Execute(delNavigationSql, new { Ids = ids });
            var relationResult = _conn.Execute(delRelationsSql, new { Ids = ids });
            transaction.Commit();
            return true;
        }

        public IEnumerable<NavigationColumn> GetAllClasses()
        {
            string sql = "select id as Id,column_name as  Name, img as Icon,on_img as OnIcon,state as State,priority as Priority,sv as Sv from `navigation_column` where is_del=0 and state=0";
            return _conn.Query<NavigationColumn>(sql);
        }

        public IEnumerable<NavigationInfo> GetAllNavigationInfo()
        {
            try
            {
                string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
                                    from `navigation_info` as n
                                    left join `navigation_info` as n2 on n2.id = n.f_id
                                    where n.is_del=0";
                return _conn.Query<NavigationInfo>(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public IEnumerable<NavigationColumn> GetAllNavigationColumn()
        {
            string sql = "select id as Id,column_name as  Name, img as Icon,on_img as OnIcon,state as State,priority as Priority,sv as Sv from `navigation_column` where is_del=0";
            return _conn.Query<NavigationColumn>(sql);
        }

        public IEnumerable<NavigationInfo> GetAllParentNavigation()
        {
            string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
                                    from `navigation_info` as n
                                    left join `navigation_info` as n2 on n2.id = n.f_id
                                    where n.is_del=0 and n.f_id=0";
            return _conn.Query<NavigationInfo>(sql);
        }

        public bool AddNavigationInfo(NavigationInfoEntity navigationInfo)
        {
            string sql = @"INSERT INTO `navigation_info` 
(web_name,logo_img,web_intro,link_addr,f_id,play_state,home_state,wechat,is_examine)
VALUES (@Name,@Logo,@Description,@Link,@ParentId,@PlayState,@HomeState,@Weixin,@Check)";
            return _conn.Execute(sql, navigationInfo) > 0; ;
        }

        public NavigationInfoEntity GetNavigationById(int id)
        {
            string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									n.f_id  as ParentId
                                    from `navigation_info` as n
                                    where n.is_del=0 and n.id=@Id";
            return _conn.QuerySingle<NavigationInfoEntity>(sql, new { Id = id });
        }

        public NavigationColumn GetNavigationColumnById(int id)
        {
            string sql = "select id as Id,column_name as  Name, img as Icon,on_img as OnIcon,state as State,priority as Priority,sv as Sv  from `navigation_column` where is_del=0 and id=@Id";
            return _conn.QuerySingle<NavigationColumn>(sql, new { Id = id });
        }

        public bool UpdateNavigationColumn(NavigationColumn navigationColumn)
        {
            string sql = "update  `navigation_column`  set column_name=@Name,img=@Icon,on_img=@OnIcon,state=@State,priority=@Priority,sv=@Sv  where id=@Id";
            return _conn.Execute(sql, navigationColumn) > 0;
        }

        public bool UpdateNavigationInfo(NavigationInfoEntity navigationInfo)
        {
            string sql = @"update  navigation_info set
                                    id =@Id,
                                    web_name =@Name, 
                                    logo_img =@Logo,
                                    web_intro =@Description,
                                    link_addr =@Link,
                                    f_id =@ParentId,
                                    play_state =@PlayState,
                                    home_state =@HomeState,
                                    wechat=@Weixin,
                                    is_examine=@Check
                                    where id=@Id";
            return _conn.Execute(sql, navigationInfo) > 0;
        }

        public IEnumerable<NavigationInfo> Search(NavigationCondition navigationCondition)
        {
            try
            {
                string tatolCountSql = @"select  count(0)  from `navigation_info`  as n where n.is_del=0";
                string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
                                    from `navigation_info` as n
                                    left join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0";
                if (!string.IsNullOrEmpty(navigationCondition.NavigationName))
                {
                    string searchName = " and n.web_name like CONCAT('%',@NavigationName,'%')";
                    sql += searchName;
                    tatolCountSql += searchName;
                }
                if (!string.IsNullOrEmpty(navigationCondition.ClassName))
                {
                    string searchClass = " and t.column_name like CONCAT('%',@ClassName,'%')";
                    sql += searchClass;
                    tatolCountSql += searchClass;
                }
                if (navigationCondition.IsCheck.HasValue)
                {
                    string searchCheck = " and n.is_examine=@IsCheck";
                    sql += searchCheck;
                    tatolCountSql += searchCheck;
                }
                navigationCondition.TotalCount = _conn.QuerySingleOrDefault<int>(tatolCountSql, new { navigationCondition.NavigationName, navigationCondition.IsCheck, navigationCondition.ClassName });
                sql += " limit @PageIndex,@PageSize";
                if (navigationCondition.IsCheck.HasValue)
                    return _conn.Query<NavigationInfo>(sql, new { navigationCondition.NavigationName, navigationCondition.ClassName, IsCheck = navigationCondition.IsCheck.Value, PageIndex = (navigationCondition.PageIndex - 1) * navigationCondition.PageSize, navigationCondition.PageSize });
                else
                    return _conn.Query<NavigationInfo>(sql, new { navigationCondition.NavigationName, navigationCondition.ClassName, PageIndex = (navigationCondition.PageIndex - 1) * navigationCondition.PageSize, navigationCondition.PageSize });
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<NavigationInfo> GetAllChildNavigation(PageBase page) {
            string tatolCountSql = @"select 
                                    count(0)
									from navigation_info as n
                                    inner join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0 and n.f_id>0 ";
            string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
									from navigation_info as n
                                    inner join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0 and n.f_id>0  limit @PageIndex,@PageSize";
            page.TotalCount = _conn.QuerySingleOrDefault<int>(tatolCountSql);
            return _conn.Query<NavigationInfo>(sql, new { PageIndex = (page.PageIndex - 1) * page.PageSize, page.PageSize });
        }
        public IEnumerable<NavigationInfo> GetAllNavigationNotInColumn(PageBase page)
        {
            string tatolCountSql = @"select 
                                    count(0)
									from navigation_info as n
                                    left join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0  and n.f_id=0 and n.id not in (select info_id from navigation_relation)";
            string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
									from navigation_info as n
                                    left join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0 and n.f_id=0 and n.id not in (select info_id from navigation_relation)  limit @PageIndex,@PageSize";
            page.TotalCount = _conn.QuerySingleOrDefault<int>(tatolCountSql);
            return _conn.Query<NavigationInfo>(sql, new {  PageIndex = (page.PageIndex - 1) * page.PageSize, page.PageSize });
        }
        public IEnumerable<NavigationInfo> GetNavigationByColumnId(NavigationCondition  navigation)
        {
            string tatolCountSql = @"select 
                                    count(0)
									from navigation_relation as r
                                    left join `navigation_info` as n on n.id=r.info_id
                                    left join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0 and r.column_id=@ColumnId";
            string sql = @"select 
                                    n.id as Id,
                                    n.web_name as  Name, 
                                    n.logo_img as Logo,
                                    n.web_intro as Description,
                                    n.link_addr as Link,
                                    n.play_state as PlayState,
                                    n.home_state as HomeState,
									n.wechat as Weixin,
									n.is_examine as `Check`,
									case n.f_id  
									when 0 then '顶级'
									else n2.web_name end as ParentName
									from navigation_relation as r
                                    left join `navigation_info` as n on n.id=r.info_id
                                    left join `navigation_info` as n2 on n2.id = n.f_id 
                                    where n.is_del=0 and r.column_id=@ColumnId  limit @PageIndex,@PageSize";
            navigation.TotalCount = _conn.QuerySingleOrDefault<int>(tatolCountSql,new { navigation.ColumnId });
            return _conn.Query<NavigationInfo>(sql, new { navigation.ColumnId,PageIndex = (navigation.PageIndex - 1) * navigation.PageSize, navigation.PageSize });
        }
    }
}
